import json
import time
from copy import deepcopy
from decimal import Decimal

from django.db import connection, transaction
from django.shortcuts import render

# Create your views here.

from django_filters.rest_framework import DjangoFilterBackend
from rest_framework import status, serializers
from rest_framework.decorators import action
from rest_framework.filters import SearchFilter, OrderingFilter
from rest_framework.response import Response
from rest_framework.viewsets import ModelViewSet

from contract.db_util.db_util import dictfetchall, DateEncoder
from contract.log_util.db_log import create_change_log, create_addition_log
from contract.models import ContractDetail, Contract, Statements, Supplier, SaleSupplier, SaleContract, \
    SaleContractDetail, SaleStatements
from contract.serializers import ContractDetailSerializer, ContractSerializer, ContractStatisticalSerializer, \
    ContractDetailSerializer_, SupplierSerializer, ListContractSerializer, SaleSupplierSerializer, \
    SaleContractSerializer, ListSaleContractSerializer, SaleContractDetailSerializer, SaleContractDetailSerializer_
from contract.log_util.log_model_viewset import LogModelViewSet
from user.models import Group
from user.serializers import UserUseGroupSerializer
from utils.datetime_util import date_add_day
from utils.jwtAuth import JWTAuthentication
from utils.pagination import Pagination
from utils.permissions import BaseAuthPermission, AllowAllPermission
from utils.utils import VisitThrottle


# 获得单个合同的结算结果 执行情况
def getSumDetailData(contractid):
    sql = '''select 
                sum_arrived_at_now + sum_deduction as sum_arrived_at_now,  
                sum_prepayments,
                sum_settlemen_prepayments,
                sum_settlemen_amount,
                sum_settlemen_prepayments/sum_settlemen_amount as average_settlement_unit,
                estimated_delivery_volume,
                estimated_delivery_volume * d.unit_price as estimated_amount_of_arrival,
                d.quantity - sum_settlemen_amount as remaining_outstanding,
                d.quantity - sum_settlemen_amount - estimated_delivery_volume	as undelivered_quantity,
                d.amount - sum_prepayments as estimate_payables,
                sum_prepayments - sum_settlemen_prepayments - ((sum_arrived_at_now-sum_settlemen_amount + sum_deduction) * d.unit_price)   as balance_of_contract,
                sum_prepayments - sum_settlemen_prepayments  as payables
                ,d.*,e.*
                from (
                   select 
                    case 
                        when sum(a.settlemen_prepayments) is null 
                        then 0
                        else sum(a.settlemen_prepayments)
                    end as sum_settlemen_prepayments,
                    case 
                        when sum(a.settlemen_amount) is null 
                        then 0
                        else sum(a.settlemen_amount)
                    end as sum_settlemen_amount,
                    case 
                        when sum(a.deduction) is null 
                        then 0
                        else sum(a.deduction)
                    end as sum_deduction,
                    case 
                        when sum(a.settlemen_unit) is null 
                        then 0
                        else sum(a.settlemen_unit)
                    end as settlemen_unit,
                    b.id as contract_id
                    from sale_contract_statements a 
                    right join sale_contract_contract b on a.contract_id = b.id
                    where b.id={contractid}
                ) as a join 
                (
                    select 
                     case 
                        when sum(arrived_at_now) is null 
                        then 0
                        else sum(arrived_at_now)
                    end as sum_arrived_at_now,
                    case 
                        when sum(prepayments) is null 
                        then 0
                        else sum(prepayments)
                    end as sum_prepayments,
                    case 
                        when contract_id is null 
                        then {contractid}
                        else contract_id
                    end as contract_id
                    from  sale_contract_detail 
                    where contract_id ={contractid}
                )
                as b on a.contract_id =b.contract_id join 
                (
                    select case 
                        when sum(arrived_at_now) is null 
                        then 0
                        else sum(arrived_at_now)
                    end  as estimated_delivery_volume,
                    case 
                        when contract_id is null 
                        then {contractid}
                        else contract_id
                    end as contract_id from sale_contract_detail
                    where statements_status = 0 and contract_id={contractid}
                )
                as c on c.contract_id = a.contract_id left join
                sale_contract_contract d on d.id = a.contract_id left join 
sale_supplier e on d.supplier_id = e.id;'''

    print(sql.format(contractid=contractid))
    cur = connection.cursor()
    cur.execute(sql.format(contractid=contractid))
    data = dictfetchall(cur)
    cur.close()
    return data


# 获得单个合同的历史结算结果 执行情况  根据时间来 进行统计
def getHistorySumDetailData(contractid, date_str):
    sql = '''
            select 
        sum_arrived_at_now + sum_deduction as sum_arrived_at_now, 
        case 
            when sum_prepayments is null 
            then 0
            else sum_prepayments
        end as sum_prepayments,  
        sum_settlemen_prepayments,
        sum_settlemen_amount,
        sum_deduction,
        sum_settlemen_prepayments/sum_settlemen_amount as average_settlement_unit,
        sum_arrived_at_now-sum_settlemen_amount + sum_deduction as estimated_delivery_volume,
        (sum_arrived_at_now-sum_settlemen_amount + sum_deduction) * d.unit_price as estimated_amount_of_arrival,
        d.quantity - sum_settlemen_amount as remaining_outstanding,
        d.quantity - sum_settlemen_amount - (sum_arrived_at_now-sum_settlemen_amount + sum_deduction) 	as undelivered_quantity,
        d.amount - sum_prepayments as estimate_payables,
        sum_prepayments - sum_settlemen_prepayments - ((sum_arrived_at_now-sum_settlemen_amount + sum_deduction) * d.unit_price)   as balance_of_contract,
        sum_prepayments - sum_settlemen_prepayments  as payables,
        d.*,
        e.*
        from (
           select 
                    case 
                        when sum(a.settlemen_prepayments) is null 
                        then 0
                        else sum(a.settlemen_prepayments)
                    end as sum_settlemen_prepayments,
                    case 
                        when sum(a.settlemen_amount) is null 
                        then 0
                        else sum(a.settlemen_amount)
                    end as sum_settlemen_amount,
                    case 
                        when sum(a.deduction) is null 
                        then 0
                        else sum(a.deduction)
                    end as sum_deduction,
                    a.settlemen_unit,
                    b.id as contract_id
                    from sale_contract_statements a 
                    right join sale_contract_contract b on a.contract_id = b.id
                    where b.id={contractid}
            and a.create_time <='{date_str}'
        ) as a join 
        (
            select case 
                        when sum(arrived_at_now) is null 
                        then 0
                        else sum(arrived_at_now)
                    end  as sum_arrived_at_now, 
            case 
                        when sum(prepayments) is null 
                        then 0
                        else sum(prepayments)
                    end as sum_prepayments,
            case 
                        when contract_id is null 
                        then {contractid}
                        else contract_id
                    end as contract_id
            from  sale_contract_detail 
            where contract_id ={contractid}
            and create_time <='{date_str}'   

        )
        as b on a.contract_id =b.contract_id join 
        sale_contract_contract d on d.id = a.contract_id left join 
        sale_supplier e on d.supplier_id = e.id
        ;
    '''
    cur = connection.cursor()
    print(sql.format(contractid=contractid, date_str=date_str))
    cur.execute(sql.format(contractid=contractid, date_str=date_str))
    data = dictfetchall(cur)
    cur.close()
    return data


# 根据合同id计算到货未计算金额
def getEstimatedPriceByContractId(contract_id):
    pass
    sql = """select * from sale_contract_contract a join sale_contract_detail b on a.id = b.contract_id 
    where b.statements_id is null and  arrived_at_now is not null and a.id = %s"""
    cur = connection.cursor()
    cur.execute(sql, [contract_id])
    data = dictfetchall(cur)

    estimated_amount_of_arrival = 0
    for item in data:
        if item['estimated_price'] is None:
            estimated_amount_of_arrival = estimated_amount_of_arrival + item['arrived_at_now'] * item['unit_price']
        else:
            estimated_amount_of_arrival = estimated_amount_of_arrival + item['arrived_at_now'] * item['estimated_price']
    return estimated_amount_of_arrival

# 合同执行情况
class SaleContractDetailViewSet(ModelViewSet):
    ''''''
    queryset = SaleContractDetail.objects.order_by('-create_time')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = SaleContractDetailSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('contract__supplier__name', 'statements__contract__inventory_name')
    ordering_fields = ('contract__supplier__name',)
    filterset_fields = ['contract_id', 'arrived_at_now']
    pagination_class = Pagination

    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getOnlyPrepayments(self, request):
        queryset = self.get_queryset().filter(prepayments__isnull=False)
        serializer = ContractDetailSerializer(queryset, many=True)
        return Response(serializer.data)

    # 提交结算结果
    @action(methods=['post'], detail=False, permission_classes=[AllowAllPermission])
    @transaction.atomic()
    def subSettlement(self, request):
        # print('request.data ------------------------------------------')
        # print(request.data)
        arrs = request.data['arrs']
        # print(type(arrs))

        temparrs = []
        flag = True
        contractDetail = None
        for item in arrs:
            contractDetail = SaleContractDetail.objects.get(id=item['id'])
            if contractDetail.statements_status == 0:
                pass
            else:
                flag = False
            temparrs.append(contractDetail)
        if contractDetail is None:
            raise serializers.ValidationError({'message': '未勾选合同执行条目，请勾选后重试'})

        contract = contractDetail.contract

        # 确保所有的 ContractDetail 未结算
        if flag:
            settlemen_amount = request.data['settlemen_amount']
            settlemen_unit = request.data['settlemen_unit']
            deduction = request.data['deduction']
            if 'note' in request.data:
                note = request.data['note']
            else:
                note = ''

            if 'rewards_or_punishments' in request.data:
                rewards_or_punishments = request.data['rewards_or_punishments']
            else:
                rewards_or_punishments = 0
            # settlemen_prepayments = request.data['settlemen_prepayments']
            settlment = SaleStatements()
            settlment.settlemen_amount = settlemen_amount
            settlment.settlemen_unit = settlemen_unit
            settlment.contract = contract
            settlment.rewards_or_punishments = Decimal.from_float(rewards_or_punishments)

            a = Decimal(settlemen_amount)
            b = Decimal(settlemen_unit)
            temp = a * b + Decimal.from_float(rewards_or_punishments)
            settlment.settlemen_prepayments = temp
            settlment.note = note
            settlment.deduction = deduction

            # print(contract.id)
            data = getSumDetailData(contract.id)

            remaining_outstanding = None
            if len(data) > 0:
                remaining_outstanding = data[0]['remaining_outstanding']

            if remaining_outstanding is not None:
                if float(a) > float(remaining_outstanding):
                    raise serializers.ValidationError({'message': '结算量超出合同的总数量，请确认后重新提交'})

            # print(settlment.__dict__)
            settlment.save()
            # 保存新增日志
            create_addition_log(request.user.id, settlment)
            # print(settlment.id)

            for item in temparrs:
                # 构建 修改日志的参数
                origin_data = deepcopy(item.__dict__)
                item.statements = settlment
                item.statements_status = 1
                item.save()
                # 保存修改日志
                create_change_log(request.user.id, item, origin_data, item.__dict__)

        else:
            raise serializers.ValidationError({'message': '包含已结算的，请确认后重新提交'})
        data = {'status': 'ok'}
        return Response(data)

    # 聚合函数 获得统计结果
    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getSumDetail(self, request):
        print(request.query_params)
        print(request.query_params['contractid'])

        contractid = request.query_params['contractid']
        if contractid is None:
            raise serializers.ValidationError({'message': '参数不完整，缺少contractid'})
        data = getSumDetailData(contractid=contractid)

        print(data)
        if len(data) > 0:
            for key in data[0]:
                if data[0][key] is None:
                    data[0][key] = 0
            return Response(data[0])
        else:
            data = {
                "sum_arrived_at_now": 0,
                "sum_prepayments": 0,
                "sum_settlemen_prepayments": 0,
                "sum_settlemen_amount": 0,
                "estimated_delivery_volume": 0,
                "balance_of_contract": 0,
            }
            return Response(data)

    # 获得 没有结算的 执行明细
    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getUnSettlementContract(self, request):
        contractid = request.query_params['contractid']

        # 条件查询 不包含arrived_at_now=0, 包含statements_status=0,arrived_at_now__isnull=False, contract=contractid
        from django.db.models import Q
        contracts = SaleContractDetail.objects.filter(~Q(arrived_at_now=0), arrived_at_now__isnull=False,
                                                  contract=contractid, statements_status=0, )
        serializer = SaleContractDetailSerializer(contracts, many=True)
        return Response(serializer.data)

    # 获得 所有合同的 统计 报表
    @action(methods=['get'], detail=False, permission_classes=[AllowAllPermission])
    def getAllStatisticalResults(self, request):
        # 累加 所有供应商 和计
        def cumulative_total_combined(total_combined, combined):
            total_combined['sum_prepayments'] = combined['sum_prepayments'] + total_combined['sum_prepayments']
            total_combined['estimate_payables'] = combined['estimate_payables'] + total_combined[
                'estimate_payables']
            total_combined['estimated_amount_of_arrival'] = combined['estimated_amount_of_arrival'] + \
                                                            total_combined['estimated_amount_of_arrival']
            total_combined['payables'] = combined['payables'] + total_combined['payables']
            total_combined['sum_settlemen_prepayments'] = combined['sum_settlemen_prepayments'] + \
                                                          total_combined['sum_settlemen_prepayments']

            total_combined['balance_of_contract'] = combined['balance_of_contract'] + \
                                                    total_combined['balance_of_contract']

        # 累加 单个供应商 小计
        def cumulative_combined(combined, temp):
            combined['sum_prepayments'] = Decimal(temp[0]['sum_prepayments']) + combined['sum_prepayments']
            combined['estimate_payables'] = Decimal(temp[0]['estimate_payables']) + combined[
                'estimate_payables']
            combined['estimated_amount_of_arrival'] = Decimal(temp[0]['estimated_amount_of_arrival']) + \
                                                      combined['estimated_amount_of_arrival']
            combined['payables'] = Decimal(temp[0]['payables']) + combined['payables']
            combined['sum_settlemen_prepayments'] = Decimal(temp[0]['sum_settlemen_prepayments']) + combined[
                'sum_settlemen_prepayments']
            combined['balance_of_contract'] = Decimal(temp[0]['balance_of_contract']) + combined['balance_of_contract']

        # -----------------------------------代码开始----------------------------------------------------

        if 'date' in request.query_params:
            date = request.query_params['date']
        else:
            # 当前时间 str
            date = time.strftime('%Y-%m-%d', time.localtime(time.time()))

        if 'contract_status' in request.query_params:
            contract_status = request.query_params['contract_status']
        else:
            contract_status = 0

        # date + 1天
        date = date_add_day(date, 1)
        data_arr = []
        if contract_status != "2":
            contracts = SaleContract.objects.filter(contract_status=contract_status).order_by("supplier_id")
        else:
            contracts = SaleContract.objects.all().order_by("supplier_id")
        # contracts = Contract.objects.filter(pk=4)
        print(len(contracts))
        # 初始化 supplier_flag，用于判断供应商是否同一个
        # 初始化 combined 小计数据  用于保存供应商 小计数据
        # 初始化 total_combined 总计数据 用于保存所有合同的 总计数据
        supplier_flag = 0
        # 小计
        combined = {'sum_prepayments': Decimal(0), 'name': '小计', 'estimate_payables': Decimal(0),
                    'estimated_amount_of_arrival': Decimal(0), 'payables': Decimal(0), 'sum_settlemen_prepayments':
                        Decimal(0), 'balance_of_contract': Decimal(0)}
        # 合计
        total_combined = {'sum_prepayments': Decimal(0), 'name': '总计', 'estimate_payables': Decimal(0),
                          'estimated_amount_of_arrival': Decimal(0), 'payables': Decimal(0),
                          'sum_settlemen_prepayments': Decimal(0), 'balance_of_contract': Decimal(0)}

        # 正负 余额
        balance_ = {'positive': Decimal(0), 'negative': Decimal(0)}
        if len(contracts) > 0:
            supplier_flag = contracts[0].supplier_id

        for i in contracts:
            # 获得统计数据
            # temp = getSumDetailData(i.id)
            temp = getHistorySumDetailData(i.id, date)

            estimate_payables = None
            payables = None
            balance_of_contract = None
            if len(temp) > 0:
                # 设置 到货未结算金额
                temp[0]['estimated_amount_of_arrival'] = getEstimatedPriceByContractId(i.id)

                # 防止空异常
                estimate_payables = temp[0]['estimate_payables']
                payables = temp[0]['payables']
                balance_of_contract = temp[0]['balance_of_contract']
                if estimate_payables is None:
                    estimate_payables = Decimal(0)
                if payables is None:
                    payables = Decimal(0)
                if balance_of_contract is None:
                    balance_of_contract = Decimal(0)

            if len(temp) > 0:
                # 合计值的处理 ------------------------------------------------------------
                # print(i.supplier_id, supplier_flag, i.supplier.name, combined['sum_prepayments'],
                #       Decimal(temp[0]['sum_prepayments']), i.supplier_id == supplier_flag)

                # 设置 到货未结算金额
                temp[0]['estimated_amount_of_arrival'] = getEstimatedPriceByContractId(i.id)

                if i.supplier_id == supplier_flag:
                    # 如果是同一个供应商 累加数据  计算合计值
                    combined['name'] = i.supplier.name + '小计'
                    cumulative_combined(combined, temp)

                else:
                    # 如果不是同一个供应商  添加累加数据到数组  设置新的 supplier_flag  初始化 combined
                    data_arr.append(combined)
                    # 累加 总计数据
                    cumulative_total_combined(total_combined=total_combined, combined=combined)
                    supplier_flag = i.supplier_id

                    combined = {'sum_prepayments': Decimal(temp[0]['sum_prepayments']), 'name': i.supplier.name + '小计',
                                'estimate_payables': estimate_payables,
                                'estimated_amount_of_arrival': Decimal(temp[0]['estimated_amount_of_arrival']),
                                'payables': payables, 'sum_settlemen_prepayments':
                                    Decimal(temp[0]['sum_settlemen_prepayments']), 'balance_of_contract':
                                    balance_of_contract}

                # 正负余额的处理  正负余额 需要分开处理
                if balance_of_contract > 0:
                    balance_['positive'] = balance_['positive'] + balance_of_contract
                else:
                    balance_['negative'] = balance_['negative'] + balance_of_contract
                data_arr.append(temp[0])
        # 对最后一次的 combined 和 total_combined 进行处理 添加累加数据到数组  添加 总计数据到数组
        combined['name'] = temp[0]['name'] + '小计'
        data_arr.append(combined)
        cumulative_total_combined(total_combined=total_combined, combined=combined)

        # 正负余额 添加到数组
        total_combined['balance'] = balance_
        data_arr.append(total_combined)
        return Response(data_arr)

    def get_serializer_class(self):
        if self.action in ['getSumDetail']:
            return ContractStatisticalSerializer
        elif self.action in ['list']:
            return SaleContractDetailSerializer
        else:
            return SaleContractDetailSerializer_

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        # 同时 删除关联的结算数据 和 结算数据关联的执行明细
        statement = instance.statements
        instance.delete()
        statement.delete()
        return Response(status=status.HTTP_204_NO_CONTENT)


# 合同管理
class SaleContractViewSet(ModelViewSet):
    '''合同管理'''
    queryset = SaleContract.objects.order_by('-supplier_name')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = SaleContractSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('supplier__name', 'inventory_name')
    ordering_fields = ('supplier_name',)
    filterset_fields = ['supplier_name', 'supplier', 'contract_status', ]
    pagination_class = Pagination

    def get_serializer_class(self):
        if self.action in ['list']:
            return ListSaleContractSerializer
        else:
            return SaleContractSerializer


# 供应商管理
class SaleSupplierViewSet(ModelViewSet):
    '''供应商管理'''
    queryset = SaleSupplier.objects.order_by('name')
    authentication_classes = (JWTAuthentication,)
    permission_classes = [BaseAuthPermission, ]
    throttle_classes = [VisitThrottle]
    serializer_class = SaleSupplierSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter,)
    search_fields = ('name',)
    ordering_fields = ('name',)
    filterset_fields = ['name']
    pagination_class = Pagination



